# 该部分集成了mysql数据库的增删改接口，因为常用的是增和改，删和查接口没有加入flask接口列表中

import pandas as pd
from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column, String, Integer, SMALLINT, DECIMAL, Enum, TEXT, TIMESTAMP

Base = declarative_base()


class TCL(Base):
    __tablename__ = "TCL_Result"

    study_id = Column(String(64), primary_key=True)
    inv_pred = Column(String(64))
    men_pred = Column(String(64))
    
    def __init__(self, study_id, inv_pred,men_pred):
        self.study_id = study_id
        self.inv_pred = inv_pred
        self.men_pred = men_pred
# 创建新表
def new_table():
  Base = declarative_base()
  engine = create_engine('mysql+pymysql://root:123456@localhost:3306/mydb')
  Base.metadata.create_all(engine)
  
# 增 方法
def sql_add():
  # 初始化数据库连接
  # 按实际情况依次填写MySQL的用户名、密码、IP地址、端口、数据库名
    engine = create_engine("mysql+pymysql://{}:{}@{}:{}/{}".format('root', '123456', 'localhost', '3306', 'mydb'))
    #建立session
    Session = sessionmaker(bind=engine)
    session = Session()
    add_result = TCL(study_id="1351232322",inv_pred="0",men_pred="1")
    session.add(add_result)
    session.commit()
    
# 改 方法
def sql_update():    
    engine = create_engine("mysql+pymysql://{}:{}@{}:{}/{}".format('root', '123456', 'localhost', '3306', 'mydb'))
    #建立session
    Session = sessionmaker(bind=engine)
    session = Session()
    session.query(TCL).filter_by(study_id=1351232322).update({'inv_pred': "1"})
    session.commit()
  
# 删 方法  
def sql_delete():
    engine = create_engine("mysql+pymysql://{}:{}@{}:{}/{}".format('root', '123456', 'localhost', '3306', 'mydb'))
    #建立session
    Session = sessionmaker(bind=engine)
    session = Session()
    
    tcl = session.query(TCL).filter(TCL.study_id=="1351232322").first()
    session.delete(tcl)
    session.commit()


def TCL_update(study_id,inv_pred,men_pred):
    engine = create_engine("mysql+pymysql://{}:{}@{}:{}/{}".format('root', '123456', 'localhost', '3306', 'mydb'))
    #建立session
    Session = sessionmaker(bind=engine)
    session = Session()
    session.query(TCL).filter_by(study_id=study_id).update({'inv_pred': inv_pred,'men_pred':men_pred})
    session.commit()
    
def TCL_add(study_id,inv_pred,men_pred):
    engine = create_engine("mysql+pymysql://{}:{}@{}:{}/{}".format('root', '123456', 'localhost', '3306', 'mydb'))
    #建立session
    Session = sessionmaker(bind=engine)
    session = Session()
    add_result = TCL(study_id=study_id,inv_pred=inv_pred,men_pred=men_pred)
    session.add(add_result)
    session.commit()

new_table()
# TCL_update("1351232322","0","0.999","11","0102")